﻿<%
Class HomeControllerMssql

	Private s_model

	'''构造
	Private Sub Class_Initialize()
		'id,share_openid,in_openid,add_time
		Set s_model = Sa.Model("[wechat_share]")
	End Sub
	
	'''析构
	Private Sub Class_Terminate()
		Set s_model = Nothing
	End Sub

	'''测试DataBase
	Public Sub tdatabase()
		Dim t_db : Set t_db = s_model.DataBase
		Dim t_rs : Set t_rs = t_db.Query("SELECT * FROM [mu_user_role]")
		Dim t_x
		While Not t_rs.Eof
			For Each t_x In t_rs.Fields
				Response.Write( t_x.Name & ":" & t_x.Value & "<br/>" )
			Next
			t_rs.MoveNext()
		Wend
		t_rs.Close
		Set t_rs = Nothing
		Set t_db = Nothing
	End Sub

	'''测试Table
	Public Sub ttable()
		Dim t_row, t_field, t_rows : t_rows = s_model.Table("user_role").Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
	End Sub

	'''测试Prefix
	Public Sub tprefix()
		Dim t_row, t_field, t_rows : t_rows = s_model.Prefix("tt_").Table("user_role").Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL)
	End Sub

	'''测试Alias
	Public Sub talias()
		Dim t_row, t_field, t_rows : t_rows = s_model.Table("user_role").Alias("t_ur").Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL)
	End Sub

	'''测试Top
	Public Sub ttop()
		Dim t_row, t_field, t_rows : t_rows = s_model.Table("user_role").Alias("t_ur").top(3).Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL)
	End Sub

	'''测试Distinct
	Public Sub tdistinct()
		Dim t_row, t_field, t_rows : t_rows = s_model.Table("user_role").Alias("t_ur").Distinct(True).field("sort").top(3).Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL)
	End Sub

	'''测试Field
	Public Sub tfield()
		Dim t_row, t_field, t_rows

		'''使用字符串来定义要返回的数据库字段
		t_rows = s_model.Table("user_role").Alias("t_ur").top(3).field("id, [name] AS realname").Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")

		'''使用数组来定义要返回的数据库字段
		Dim t_fields(1)
		t_fields(0) = "id"
		t_fields(1) = "[name] AS realname"
		t_rows = s_model.Table("user_role").Alias("t_ur").top(3).field(t_fields).Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")
	End Sub

	'''测试Join
	Public Sub tjoin()
		Dim t_row, t_field, t_rows

		'''使用字符串来Join
		t_rows = s_model.Table("user_user").Alias("t_uu").Join("LEFT JOIN [mu_user_role] AS t_ur ON t_uu.role_id = t_ur.id").field("user_id, realname, t_ur.name AS role_name").top(3).Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")

		'''使用数组来Join
		Dim t_joins(1)
		t_joins(0) = "LEFT JOIN [mu_user_role] AS t_ur ON t_uu.role_id = t_ur.id"
		t_joins(1) = "LEFT JOIN (SELECT user_id, SUM(score) AS score FROM mu_user_score GROUP BY user_id) AS t_us ON t_uu.user_id = t_us.user_id"
		t_rows = s_model.Table("user_user").Alias("t_uu").Join(t_joins).field("t_uu.user_id, t_uu.realname, t_ur.name AS role_name, IIF(t_us.score=NULL, 0, t_us.score) AS score").top(5).Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")
	End Sub

	'''测试Union
	'''由于数据类型 text 不可比，所以不能将它用作 UNION、INTERSECT 或 EXCEPT 等运算符的操作数。 
	Public Sub tunion()
		Dim t_row, t_field, t_rows

		'''使用字符串来Union
		t_rows = s_model.Table("user_role").Alias("t_ur1").Field("[ident], [name]").Union("SELECT [ident], [name] FROM tt_user_role WHERE status=1").Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")

		'''使用字符串来多次Union
		t_rows = s_model.Table("user_role").Field("[ident], [name]").Union("SELECT [ident], [name] FROM tt_user_role WHERE status=1").Union("SELECT [ident], [name] FROM tt_user_role WHERE status=0").Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")

		'''使用数组来Join
		Dim t_unions(1)
		t_unions(0) = "SELECT [ident], [name] FROM tt_user_role WHERE status=1"
		t_unions(1) = "SELECT [ident], [name] FROM tt_user_role WHERE status=0"
		t_rows = s_model.Table("user_role").Field("[ident], [name]").Union(t_unions).Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")
	End Sub

	'''测试Union
	Public Sub tunionall()
		Dim t_row, t_field, t_rows

		'''使用字符串来Union
		t_rows = s_model.Table("user_role").Alias("t_ur1").Field("[ident], [name]").UnionAll("SELECT [ident], [name] FROM tt_user_role WHERE status=1").Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")

		'''使用字符串来多次UnionAll
		t_rows = s_model.Table("user_role").Field("[ident], [name]").UnionAll("SELECT [ident], [name] FROM tt_user_role WHERE status=1").UnionAll("SELECT [ident], [name] FROM tt_user_role WHERE status=0").Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")

		'''使用数组来Join
		Dim t_unions(1)
		t_unions(0) = "SELECT [ident], [name] FROM tt_user_role WHERE status=1"
		t_unions(1) = "SELECT [ident], [name] FROM tt_user_role WHERE status=0"
		t_rows = s_model.Table("user_role").Field("[ident], [name]").UnionAll(t_unions).Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")
	End Sub

	'''测试Where
	Public Sub twhere()
		Dim t_row, t_field, t_rows
		t_rows = s_model.Table("user_role").Where("pid=1").Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")

		t_rows = s_model.Table("user_role").Where("pid=1").Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")
	End Sub

	'''测试Order
	Public Sub torder()
		Dim t_row, t_field, t_rows
		t_rows = s_model.Table("user_role").Where("pid=1").Order("id").Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")
		
		t_rows = s_model.Table("user_role").Where("pid=1").Order("[id] DESC").Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")
	End Sub

	'''测试Size
	Public Sub tsize()
		Dim t_row, t_field, t_rows

		Redim t_rows(-1)
		
		t_rows = s_model.Table("user_user").Order("user_id ASC").Size(10).Page(27).Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & "[:]" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")

		'''Size是和Page方法一起配合使用的，仅仅调用Size不会起到什么作用
		t_rows = s_model.Table("user_user").Size(10).Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				'Response.Write( t_field & ":" & t_row(t_field) & "<br/>" )
			Next
		Next
		'Response.Write(s_model.LastSQL & "<br/>")
	End Sub

	'''测试Count
	Public Sub tcount()
		Dim t_row, t_field, t_rows

		Redim t_rows(-1)
		
		t_rows = s_model.Table("user_user").Order("user_id ASC").Size(10).Page(27).Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & "[:]" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.Count & "<br/>")
		Response.Write(s_model.LastSQL & "<br/>")
	End Sub

	'''测试Pages
	Public Sub tpages()
		Dim t_row, t_field, t_rows

		Redim t_rows(-1)
		
		t_rows = s_model.Table("user_user").Order("user_id ASC").Size(10).Page(27).Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & "[:]" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.Pages & "<br/>")
		Response.Write(s_model.LastSQL & "<br/>")
	End Sub

	'''测试Find
	Public Sub tfind()
		Dim t_row, t_field
		Set t_row = s_model.Table("user_user").Where("user_id=1").Find()
		If Has( t_row ) Then
			For Each t_field In t_row
				Response.Write( t_field & "[:]" & t_row(t_field) & "<br/>" )
			Next
		Else
			Response.Write("找不到该记录<br/>")
		End If
		
		Response.Write(s_model.LastSQL & "<br/>")
	End Sub

	'''测试Group
	Public Sub tgroup()
		Dim t_row, t_field, t_rows

		Redim t_rows(-1)
		t_rows = s_model.Table("user_user").Group("role_id").Field("role_id").Where("role_id > 20").Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & "[:]" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")

		Redim t_rows(-1)
		t_rows = s_model.Table("user_user").Group("role_id, boss_id").Field("role_id, boss_id").Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & "[:]" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")
	End Sub

	'''测试Having
	Public Sub thaving()
		Dim t_row, t_field, t_rows

		Redim t_rows(-1)
		t_rows = s_model.Table("user_user").Group("role_id").Field("role_id").Having("role_id > 20").Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & "[:]" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")

		Redim t_rows(-1)
		t_rows = s_model.Table("user_user").Group("role_id, boss_id").Having("role_id < 20 AND boss_id=0").Field("role_id, boss_id").Select()
		For Each t_row In t_rows
			For Each t_field In t_row
				Response.Write( t_field & "[:]" & t_row(t_field) & "<br/>" )
			Next
		Next
		Response.Write(s_model.LastSQL & "<br/>")
	End Sub

	'''测试Max
	Public Sub tmax()
		Dim t_max
		t_max = s_model.Table("user_user").Max("user_id")
		Response.Write(s_model.LastSQL & "<br/>")
		Response.Write(t_max & "<br/>")
		'''可以加入条件搜索
		t_max = s_model.Table("user_user").Where("user_id<100").Max("user_id")
		Response.Write(s_model.LastSQL & "<br/>")
		Response.Write(t_max & "<br/>")
	End Sub

	'''测试Min
	Public Sub tmin()
		Dim t_min
		t_min = s_model.Table("user_user").Min("user_id")
		Response.Write(s_model.LastSQL & "<br/>")
		Response.Write(t_min & "<br/>")
		'''可以加入条件搜索
		t_min = s_model.Table("user_user").Where("user_id>100").Min("user_id")
		Response.Write(s_model.LastSQL & "<br/>")
		Response.Write(t_min & "<br/>")
	End Sub

	'''测试Min
	Public Sub tavg()
		Dim t_avg
		t_avg = s_model.Table("user_user").Avg("user_id")
		Response.Write(s_model.LastSQL & "<br/>")
		Response.Write(t_avg & "<br/>")
		'''可以加入条件搜索
		t_avg = s_model.Table("user_user").Where("user_id>100").Avg("user_id")
		Response.Write(s_model.LastSQL & "<br/>")
		Response.Write(t_avg & "<br/>")
	End Sub

	'''测试Min
	Public Sub tsum()
		Dim t_sum
		t_sum = s_model.Table("user_user").Sum("user_id")
		Response.Write(s_model.LastSQL & "<br/>")
		Response.Write(t_sum & "<br/>")
		'''可以加入条件搜索
		t_sum = s_model.Table("user_user").Where("user_id>100").Sum("user_id")
		Response.Write(s_model.LastSQL & "<br/>")
		Response.Write(t_sum & "<br/>")
	End Sub

	'''测试Add
	Public Sub tadd()
		Dim t_dict : Set t_dict = Sa.Dictionary
		t_dict("share_openid") = "222openid1_share"
		t_dict("in_openid") = "222openid2_in"
		t_dict("add_time") = 123456789
		Dim t_id
		t_id = s_model.Add(t_dict)
		Response.Write(CStr(t_id) & "呵呵呵")
	End Sub

	'''测试Update
	Public Sub tupdate()
		Dim t_dict : Set t_dict = Sa.Dictionary
		t_dict("share_openid") = "openid2_share"
		t_dict("in_openid") = "openid2_in"
		t_dict("add_time") = 1234567890
		Dim t_num
		t_num = s_model.Update(t_dict)
		Response.Write(t_num & "呵呵呵<br/>")
		t_num = s_model.Where("id>4").Update(t_dict)
		Response.Write(t_num & "呵2呵2呵2<br/>")
	End Sub

	'''测试Delete
	Public Sub tdelete()
		Dim t_num
		t_num = s_model.Where("id>8").Delete()
		Response.Write(t_num & "呵2呵2呵2<br/>")
		t_num = s_model.Where("id>4").Delete()
		Response.Write(t_num & "呵3呵3呵3<br/>")
	End Sub
End Class
%>